package dr.zipcode.dao;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.sql.*; 
import javax.naming.*;

import dr.zipcode.domain.ZipCode;
 
public class ZipCodeDAO {
    
 	private static ZipCodeDAO instance = new ZipCodeDAO();
    
    public static ZipCodeDAO getInstance() {
        return instance;
    }
    
    private ZipCodeDAO() {
    }
     
    private Connection getConnection() throws Exception {
      Context initCtx = new InitialContext();
      Context envCtx = (Context) initCtx.lookup("java:comp/env");
      DataSource ds = (DataSource)envCtx.lookup("jdbc/orcl");
      return ds.getConnection();
    }
  
    public int selectZipCodeCount(String keyField, String keyWord)throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = null;

        int x=0;

        try {
            conn = getConnection();
            if(keyField.equals("code")){
            	sql = "select count(*) from zipcode where code like ?";
            	pstmt = conn.prepareStatement(sql);
            	pstmt.setString(1, "%"+keyWord+"%");
            }else{
            	sql = "select count(*) from zipcode where dong like ? or ri like ? or bldg like ?";
            	pstmt = conn.prepareStatement(sql);
            	pstmt.setString(1, "%"+keyWord+"%");
            	pstmt.setString(2, "%"+keyWord+"%");
            	pstmt.setString(3, "%"+keyWord+"%");
            }
        	rs = pstmt.executeQuery();

            if (rs.next()) {
               x= rs.getInt(1);
			}
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) try { rs.close(); } catch(SQLException ex) {}
            if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
        }
		return x;
    }
    
	public List<ZipCode> selectZipCodeList(int start, int end, String keyField, String keyWord)
    throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<ZipCode> zipList=null;
        String sql = null;
        
        try {
            conn = getConnection();
            if(keyField.equals("code")){
            	sql = "select code, sido, gu, dong, nvl(ri,' ') ri, nvl(dosu,' ') dosu, nvl(bunji,' ') bunji, nvl(bldg,' ') bldg from " +
            		  "(select a.*, rownum rnum from " +
            		  "(select * from zipcode " +
            		  "where code like ? " +
            		  "order by code asc)a) " +
            		  "where rnum >= ? and rnum <=?";
            	
                pstmt = conn.prepareStatement(sql);
            	pstmt.setString(1, "%"+keyWord+"%");
            	pstmt.setInt(2, start);
            	pstmt.setInt(3, end);
            }else{
            	sql = "select code, sido, gu, dong, nvl(ri,' ') ri, nvl(dosu,' ') dosu, nvl(bunji,' ') bunji, nvl(bldg,' ') bldg from " +
		  		  "(select a.*, rownum rnum from " +
		  		  "(select * from zipcode " +
		  		  "where dong like ? or ri like ? or bldg like ? " +
		  		  "order by code asc)a) " +
		  		  "where rnum >= ? and rnum <=?";
      	
            	pstmt = conn.prepareStatement(sql);
            	pstmt.setString(1, "%"+keyWord+"%");
            	pstmt.setString(2, "%"+keyWord+"%");
            	pstmt.setString(3, "%"+keyWord+"%");
            	pstmt.setInt(4, start);
            	pstmt.setInt(5, end);	
            }
            	
            rs = pstmt.executeQuery();
            zipList = new ArrayList<ZipCode>();
            while(rs.next()) {
                  ZipCode zip= new ZipCode();
                  
                  zip.setCode(rs.getString("code"));
      		      zip.setSido(rs.getString("sido"));
      		      zip.setGu(rs.getString("gu"));
      		      zip.setDong(rs.getString("dong"));
      		      zip.setRi(rs.getString("ri"));
      		      zip.setDosu(rs.getString("dosu"));
      		      zip.setBunji(rs.getString("bunji"));
      		      zip.setBldg(rs.getString("bldg"));
				  
                  zipList.add(zip);
			    }
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) try { rs.close(); } catch(SQLException ex) {}
            if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
        }
		return zipList;
    }
 }